package com.yudean.esb.dao.esb;

import com.yudean.esb.domain.esb.EsbDataSource;
import com.yudean.esb.mapper.esb.EsbDataSourceMapper;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 获取系统数据源的配置信息
 *
 * @author : DL<duanlei@gdyd.com>
 * @version : 1.0
 */
@Repository
public class EsbDataSourceDao {
    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(EsbDataSourceDao.class);
    @Resource
    private EsbDataSourceMapper esbDataSourceMapper;

    public List<EsbDataSource> getAll() {
        EsbDataSource dataSource = null;
        List<EsbDataSource> list = esbDataSourceMapper.select(dataSource);
        if (list == null || list.size() < 1)
            return null;

        return list;
    }

    public List<EsbDataSource> getAllEnable() {
        EsbDataSource dataSource = null;
        List<EsbDataSource> list = esbDataSourceMapper.selectEnable(dataSource);
        if (list == null || list.size() < 1)
            return null;

        return list;
    }

    public EsbDataSource get(String ID) {
        if (ID == null)
            return null;
        else
            return esbDataSourceMapper.selectOne(ID);
    }

    public void add(EsbDataSource dataSource) {
        if (dataSource == null)
            return;
        esbDataSourceMapper.insert(dataSource);
    }

    public void addBatch(List<EsbDataSource> dataSourceList) {
        if (dataSourceList == null || dataSourceList.size() < 1)
            return;
        esbDataSourceMapper.addBatch(dataSourceList);
    }

    public void update(EsbDataSource dataSource) {
        if (dataSource == null)
            return;
        esbDataSourceMapper.update(dataSource);
    }

    public void update(List<EsbDataSource> dataSourceList) {
        if (dataSourceList == null || dataSourceList.size() < 1)
            return;
        for (EsbDataSource dataSource : dataSourceList)
            update(dataSource);
    }

    public void delete(String dataSource) {
        if (dataSource == null)
            return;
        esbDataSourceMapper.delete(dataSource);
    }

    public void delBatch(String[] dataSourceList) {
        if (dataSourceList == null || dataSourceList.length < 1)
            return;
        esbDataSourceMapper.delBatch(dataSourceList);
    }

    public List<String> getTablesByDataSourceId(String dataSourceId) {
        EsbDataSource dataSource = new EsbDataSource();
        dataSource.setID(dataSourceId);
        List<EsbDataSource> list = esbDataSourceMapper.select(dataSource);
        if (list == null || list.size() < 1) {
            return null;
        }
        dataSource = list.get(0);
        String driver = dataSource.getJDBCClass();
        String url = dataSource.getJDBCURL();
        String username = dataSource.getJDBCUser();
        String password = dataSource.getJDBCPassword();
        String schemaPattern = dataSource.getSchemaPattern();
        Connection conn = getJdbcConnection(driver, url, username, password);
        return getTablesByConnection(conn, schemaPattern);
    }

    public List<String> getTablesByConnection(String driver, String url, String username, String password) {
        Connection conn = getJdbcConnection(driver, url, username, password);
        List<String> result = new ArrayList<String>();
        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getTables(null, meta.getUserName(), "%", null);
            while (rs.next()) {
                String tt = rs.getString("TABLE_NAME");
                // 去除Oracle中出現BIN$的表名
                if (tt.indexOf("BIN$") != 0) {
                    result.add(tt);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        return result;
    }

    public List<String> getTablesByConnection(Connection conn, String schemaPattern) {
        List<String> result = new ArrayList<String>();
        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = null;
            if (schemaPattern == null || schemaPattern.trim().length() == 0) {
                rs = meta.getTables(null, meta.getUserName(), "%", null);
            } else {
                rs = meta.getTables(null, null, null, null);
            }
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                String tt = rs.getString("TABLE_NAME");
                // 去除Oracle中出現BIN$的表名
                if (!tt.contains("$") && !tt.contains("/") && !tt.startsWith("DBMS")
                        && !tt.startsWith("DBA") && !tt.startsWith("EXU") && !tt.startsWith("USER")
                        && !tt.startsWith("GEN") && !tt.startsWith("SQL")) {
                    result.add(tt);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        return result;
    }

    private Connection getJdbcConnection(String driver, String url, String username, String password) {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获得数据表字段名称
     *
     * @param dataSourceId 数据源ID
     * @param tableName    表名
     * @return
     */
    public List<String> getColumns(String dataSourceId, String tableName) {
        EsbDataSource dataSource = new EsbDataSource();
        dataSource.setID(dataSourceId);
        List<EsbDataSource> list = esbDataSourceMapper.select(dataSource);
        if (list == null || list.size() < 1) {
            return null;
        }
        dataSource = list.get(0);
        String driver = dataSource.getJDBCClass();
        String url = dataSource.getJDBCURL();
        String username = dataSource.getJDBCUser();
        String password = dataSource.getJDBCPassword();
        String schemaPattern = dataSource.getSchemaPattern();
        Connection conn = null;
        List<String> result = new ArrayList<String>();
        PreparedStatement pst = null;
        try {
            conn = getJdbcConnection(driver, url, username, password);
            //简要构造查询SQL
            StringBuffer sb = new StringBuffer("SELECT * FROM ");
            sb.append(tableName).append(" WHERE 1=2");
            pst = conn.prepareStatement(sb.toString());
            ResultSetMetaData colMetaData = pst.executeQuery().getMetaData();//meta.getColumns(null, meta.getUserName(), tableName, null);
            //if (schemaPattern == null || schemaPattern.trim().length() == 0) {
            //    rsColumns = meta.getColumns(null, meta.getUserName(), tableName, null);
            //} else {
            //    rsColumns = meta.getColumns(null, null, tableName, null);
            //}
            if (colMetaData == null) {
                return null;
            }
            int colCount = colMetaData.getColumnCount();
            for (int i = 1; i <= colCount; i++) {
                String columnName = colMetaData.getColumnName(i);
                result.add(columnName);
            }
            //while (rsColumns.next()) {
            //    result.add(rsColumns.getString("COLUMN_NAME"));
            //}
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        return result;
    }

    /**
     * 根据数据源ID 获得字段名以及其类型
     *
     * @param dataSourceId 数据源ID
     * @param tableName    目标表
     * @return
     */
    public Map<String, String> getColumnAndType(String dataSourceId, String tableName) {

        EsbDataSource dataSource = new EsbDataSource();
        dataSource.setID(dataSourceId);
        List<EsbDataSource> list = esbDataSourceMapper.select(dataSource);
        if (list == null || list.size() < 1) {
            return null;
        }
        dataSource = list.get(0);
        String driver = dataSource.getJDBCClass();
        String url = dataSource.getJDBCURL();
        String username = dataSource.getJDBCUser();
        String password = dataSource.getJDBCPassword();
        Connection conn = null;
        Map<String, String> result = new HashMap<String, String>();
        PreparedStatement pst = null;
        try {
            conn = getJdbcConnection(driver, url, username, password);
            //简要构造查询SQL
            StringBuffer sb = new StringBuffer("SELECT * FROM ");
            sb.append(tableName).append(" WHERE 1=2");
            pst = conn.prepareStatement(sb.toString());
            ResultSetMetaData colMetaData = pst.executeQuery().getMetaData();//meta.getColumns(null, meta.getUserName(), tableName, null);
            if (colMetaData == null) {
                return null;
            }
            int colCount = colMetaData.getColumnCount();
            for (int i = 1; i <= colCount; i++) {
                String columnName = colMetaData.getColumnName(i);
                String typeName = colMetaData.getColumnTypeName(i);
                result.put(columnName, typeName);
            }
            //while (rsColumns.next()) {
            //    result.put(rsColumns.getString("COLUMN_NAME"), rsColumns.getString("TYPE_NAME"));
            //}
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        return result;
    }

    /**
     * 通过查询的方式获得连接
     *
     * @param conn     数据库连接
     * @param targetTB 目标表
     * @return
     */
    private ResultSet getColMetaData(Connection conn, String targetTB) {
        StringBuffer sb = new StringBuffer("SELECT * FROM ");
        sb.append(targetTB).append(" WHERE 1=2");
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = conn.prepareStatement(sb.toString());
            return pst.executeQuery();
        } catch (SQLException e) {
            logger.error("查询字段信息出现异常！err-{}", e.getMessage(), e.getCause());
            return null;
        } finally {
            try {
                pst.close();
                //rs.close();
            } catch (Exception ex) {
            }
        }
    }
}
